Source for file SC_CustomerList.php

Documentation is available at SC_CustomerList.php

  1. <?php
  2. /*
  3.  * This file is part of EC-CUBE
  4.  *
  5.  * Copyright(c) 2000-2007 LOCKON CO.,LTD. All Rights Reserved.
  6.  *
  7.  * http://www.lockon.co.jp/
  8.  *
  9.  * This program is free software; you can redistribute it and/or
  10.  * modify it under the terms of the GNU General Public License
  11.  * as published by the Free Software Foundation; either version 2
  12.  * of the License, or (at your option) any later version.
  13.  *
  14.  * This program is distributed in the hope that it will be useful,
  15.  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  16.  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  17.  * GNU General Public License for more details.
  18.  *
  19.  * You should have received a copy of the GNU General Public License
  20.  * along with this program; if not, write to the Free Software
  21.  * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  22.  */
  23.  
  24. /*  [名称] SC_CustomerList
  25.  *  [概要] 会員検索用クラス
  26.  */
  27. class SC_CustomerList extends SC_SelectSql {
  28.  
  29.     var $arrColumnCSV;
  30.             
  31.     function SC_CustomerList($array, $mode = '') {
  32.         parent::SC_SelectSql($array);
  33.         
  34.         $masterData = new SC_DB_MasterData_Ex();
  35.         $arrMobileDomain = $masterData->getMasterData("mtb_mobile_domain");
  36.         
  37.         $objDb = new SC_Helper_DB_Ex();
  38.  
  39.         if($mode == "") {
  40.             // 会員本登録会員で削除していない会員
  41.             $this->setWhere("status = 2 AND del_flg = 0 ");
  42.             // 登録日を示すカラム
  43.             $regdate_col = 'dtb_customer.update_date';
  44.         }
  45.  
  46.         if($mode == "customer") {
  47.             // 管理機能顧客検索の場合仮登録会員も検索
  48.             //$this->setWhere( "(status = 1 OR status = 2) AND del_flg = 0 ");
  49.             $this->setWhere( " del_flg = 0 ");
  50.             // 登録日を示すカラム
  51.             $regdate_col = 'dtb_customer.update_date';
  52.         }
  53.  
  54.         // メールマガジンの場合
  55.         if($mode == "magazine") {
  56.             $this->setWhere("(del_flg = 0 OR del_flg IS NULL)");
  57.             $this->setWhere("status = 2");
  58.             // 登録日を示すカラム
  59.             $regdate_col = 'dtb_customer.create_date';
  60.         }
  61.  
  62.         // 顧客ID
  63.         if (!isset($this->arrSql['customer_id'])) $this->arrSql['customer_id'] = "";
  64.         if ( strlen($this->arrSql['customer_id']) > 0 ) {
  65.             $this->setWhere( "customer_id =  ?" );
  66.             $this->arrVal[] = $this->arrSql['customer_id'];
  67.         }
  68.  
  69.         // 名前
  70.         if (!isset($this->arrSql['name'])) $this->arrSql['name'] = "";
  71.         if ( strlen($this->arrSql['name']) > 0 ) {
  72.             if(DB_TYPE == "pgsql"){
  73.                 $this->setWhere("(name01 || name02 LIKE ?)" );
  74.             }elseif(DB_TYPE == "mysql"){
  75.                 $this->setWhere("concat(name01,name02) LIKE ?" );
  76.             }
  77.  
  78.             $searchName = $this->addSearchStr($this->arrSql['name']);
  79.             $this->arrVal[] = mb_ereg_replace("[ 　]+","",$searchName);
  80.         }
  81.  
  82.         //　名前（カナ）
  83.         if (!isset($this->arrSql['kana'])) $this->arrSql['kana'] = "";
  84.         if ( strlen($this->arrSql['kana']) > 0 ) {
  85.             if(DB_TYPE == "pgsql"){
  86.                 $this->setWhere("(kana01 || kana02 LIKE ?)");
  87.             }elseif(DB_TYPE == "mysql"){
  88.                 $this->setWhere("concat(kana01,kana02) LIKE ?" );
  89.             }
  90.             $searchKana = $this->addSearchStr($this->arrSql['kana']);
  91.             $this->arrVal[] = mb_ereg_replace("[ 　]+","",$searchKana);
  92.         }
  93.  
  94.         //　都道府県
  95.         if (!isset($this->arrSql['pref'])) $this->arrSql['pref'] = "";
  96.         if ( strlen($this->arrSql['pref']) > 0 ) {
  97.             $this->setWhere( "pref = ?" );
  98.             $this->arrVal[] = $this->arrSql['pref'];
  99.         }
  100.  
  101.         //　電話番号
  102.         if (!isset($this->arrSql['tel'])) $this->arrSql['tel'] = "";
  103.         if ( is_numeric( $this->arrSql['tel'] ) ) {
  104.             if(DB_TYPE == "pgsql"){
  105.                 $this->setWhere( "(tel01 || tel02 || tel03 LIKE ?)" );
  106.             }elseif(DB_TYPE == "mysql"){
  107.                 $this->setWhere("concat(tel01,tel02,tel03) LIKE ?" );
  108.             }
  109.             $searchTel = $this->addSearchStr($this->arrSql['tel']);
  110.             $this->arrVal[] = ereg_replace("-", "", $searchTel);
  111.         }
  112.  
  113.         //性別
  114.         if (!isset($this->arrSql['sex'])) $this->arrSql['sex'] = "";
  115.         if ( is_array( $this->arrSql['sex'] ) ){
  116.             $arrSexVal = $this->setItemTerm( $this->arrSql['sex'] ,"sex" );
  117.             foreach ($arrSexVal as $data) {
  118.                 $this->arrVal[] = $data;
  119.             }
  120.         }
  121.  
  122.         //職業
  123.         if (!isset($this->arrSql['job'])) $this->arrSql['job'] = "";
  124.         if ( is_array( $this->arrSql['job'] ) ){
  125.             if ( in_array("不明", $this->arrSql['job'] ) ) {
  126.                 $arrJobVal = $this->setItemTermWithNull( $this->arrSql['job'] ,"job" );
  127.             } else {
  128.                 $arrJobVal = $this->setItemTerm( $this->arrSql['job'] ,"job" );
  129.             }
  130.             if (is_array($arrJobVal)) {
  131.                 foreach ($arrJobVal as $data) {
  132.                     $this->arrVal[] = $data;
  133.                 }
  134.             }
  135.         }
  136.  
  137.         //　E-MAIL
  138.         if (!isset($this->arrSql['email'])) $this->arrSql['email'] = "";
  139.         if (strlen($this->arrSql['email']) > 0) {
  140.             //カンマ区切りで複数の条件指定可能に
  141.             $this->arrSql['email'] = explode(",", $this->arrSql['email']);
  142.             $sql_where = "";
  143.             foreach($this->arrSql['email'] as $val) {
  144.                 $val = trim($val);
  145.                 //検索条件を含まない
  146.                 if($this->arrSql['not_emailinc'] == '1') {
  147.                     if($sql_where == "") {
  148.                         $sql_where .= "dtb_customer.email NOT ILIKE ? ";
  149.                     } else {
  150.                         $sql_where .= "AND dtb_customer.email NOT ILIKE ? ";
  151.                     }
  152.                 } else {
  153.                     if($sql_where == "") {
  154.                         $sql_where .= "dtb_customer.email ILIKE ? ";
  155.                     } else {
  156.                         $sql_where .= "OR dtb_customer.email ILIKE ? ";
  157.                     }
  158.                 }
  159.                 $searchEmail = $this->addSearchStr($val);
  160.                 $this->arrVal[] = $searchEmail;
  161.             }
  162.             $this->setWhere($sql_where);
  163.         }
  164.  
  165.         //　E-MAIL(mobile)
  166.         if (!isset($this->arrSql['email_mobile'])) $this->arrSql['email_mobile'] = "";
  167.         
  168.         if (strlen($this->arrSql['email_mobile']) > 0) {
  169.             //カンマ区切りで複数の条件指定可能に
  170.             $this->arrSql['email_mobile'] = explode(",", $this->arrSql['email_mobile']);
  171.             $sql_where = "";
  172.             foreach($this->arrSql['email_mobile'] as $val) {
  173.                 $val = trim($val);
  174.                 //検索条件を含まない
  175.                 if($this->arrSql['not_email_mobileinc'] == '1') {
  176.                     if($sql_where == "") {
  177.                         $sql_where .= "dtb_customer.email_mobile NOT ILIKE ? ";
  178.                     } else {
  179.                         $sql_where .= "AND dtb_customer.email_mobile NOT ILIKE ? ";
  180.                     }
  181.                 } else {
  182.                     if($sql_where == "") {
  183.                         $sql_where .= "dtb_customer.email_mobile ILIKE ? ";
  184.                     } else {
  185.                         $sql_where .= "OR dtb_customer.email_mobile ILIKE ? ";
  186.                     }
  187.                 }
  188.                 $searchemail_mobile = $this->addSearchStr($val);
  189.                 $this->arrVal[] = $searchemail_mobile;
  190.             }
  191.             $this->setWhere($sql_where);
  192.         }
  193.  
  194.         //　配信メールアドレス種別
  195.         if ( $mode == 'magazine' ){
  196.             if (!isset($this->arrSql['mail_type'])) $this->arrSql['mail_type'] = "";
  197.             // PCサイトメールが指定されている場合
  198.             if ( strlen($this->arrSql['mail_type']) > 0 && $this->arrSql['mail_type'] == 1) {
  199.                 // 携帯ドメインを外す。
  200.                 foreach($arrMobileDomain as $mobile_domain) {
  201.                     $this->setWhere(" dtb_customer.email NOT ILIKE '%$mobile_domain' ");                    
  202.                 }
  203.             // 携帯サイトメールが指定されている場合
  204.             } else if( strlen($this->arrSql['mail_type']) > 0 && $this->arrSql['mail_type'] == 2) {
  205.                 $this->setWhere( " dtb_customer.email_mobile <> ''  ");
  206.             }
  207.         }
  208.  
  209.         //　HTML-mail
  210.         if ( $mode == 'magazine' ){
  211.             if (!isset($this->arrSql['htmlmail'])) $this->arrSql['htmlmail'] = "";
  212.             if ( strlen($this->arrSql['htmlmail']) > 0 ) {
  213.                 $this->setWhere( " mailmaga_flg = ? ");
  214.                 $this->arrVal[] = $this->arrSql['htmlmail'];
  215.             } else {
  216.                 $this->setWhere( " (mailmaga_flg = 1 or mailmaga_flg = 2) ");
  217.             }
  218.         }
  219.  
  220.         // 購入金額指定
  221.         if (!isset($this->arrSql['buy_total_from'])) $this->arrSql['buy_total_from'] = "";
  222.         if (!isset($this->arrSql['buy_total_to'])) $this->arrSql['buy_total_to'] = "";
  223.         if( is_numeric( $this->arrSql["buy_total_from"] ) || is_numeric( $this->arrSql["buy_total_to"] ) ) {
  224.             $arrBuyTotal = $this->selectRange($this->arrSql["buy_total_from"], $this->arrSql["buy_total_to"], "buy_total");
  225.             foreach ($arrBuyTotal as $data1) {
  226.                 $this->arrVal[] = $data1;
  227.             }
  228.         }
  229.  
  230.         // 購入回数指定
  231.         if (!isset($this->arrSql['buy_times_from'])) $this->arrSql['buy_times_from'] = "";
  232.         if (!isset($this->arrSql['buy_times_to'])) $this->arrSql['buy_times_to'] = "";
  233.         if( is_numeric( $this->arrSql["buy_times_from"] ) || is_numeric( $this->arrSql["buy_times_to"] ) ) {
  234.             $arrBuyTimes = $this->selectRange($this->arrSql["buy_times_from"], $this->arrSql["buy_times_to"], "buy_times");
  235.             foreach ($arrBuyTimes as $data2) {
  236.                 $this->arrVal[] = $data2;
  237.             }
  238.         }
  239.  
  240.         // 誕生日期間指定
  241.         if (!isset($this->arrSql['b_start_year'])) $this->arrSql['b_start_year'] = "";
  242.         if (!isset($this->arrSql['b_start_month'])) $this->arrSql['b_start_month'] = "";
  243.         if (!isset($this->arrSql['b_start_day'])) $this->arrSql['b_start_day'] = "";
  244.         if (!isset($this->arrSql['b_end_year'])) $this->arrSql['b_end_year'] = "";
  245.         if (!isset($this->arrSql['b_end_month'])) $this->arrSql['b_end_month'] = "";
  246.         if (!isset($this->arrSql['b_end_day'])) $this->arrSql['b_end_day'] = "";
  247.         if ( (strlen($this->arrSql['b_start_year']) > 0 && strlen($this->arrSql['b_start_month']) > 0 && strlen($this->arrSql['b_start_day']) > 0) ||
  248.               strlen($this->arrSql['b_end_year']) > 0 && strlen($this->arrSql['b_end_month']) > 0 && strlen($this->arrSql['b_end_day']) > 0) {
  249.  
  250.             $arrBirth = $this->selectTermRange($this->arrSql['b_start_year'], $this->arrSql['b_start_month'], $this->arrSql['b_start_day']
  251.                       , $this->arrSql['b_end_year'], $this->arrSql['b_end_month'], $this->arrSql['b_end_day'], "birth");
  252.             if (is_array($arrBirth)) {
  253.                 foreach ($arrBirth as $data3) {
  254.                     $this->arrVal[] = $data3;
  255.                 }
  256.             }
  257.         }
  258.  
  259.         // 誕生月の検索
  260.         if (!isset($this->arrSql['birth_month'])) $this->arrSql['birth_month'] = "";
  261.         if (is_numeric($this->arrSql["birth_month"])) {
  262.             $this->setWhere(" EXTRACT(month from birth) = ?");
  263.             $this->arrVal[] = $this->arrSql["birth_month"];
  264.         }
  265.  
  266.         // 登録期間指定
  267.         if (!isset($this->arrSql['start_year'])) $this->arrSql['start_year'] = "";
  268.         if (!isset($this->arrSql['start_month'])) $this->arrSql['start_month'] = "";
  269.         if (!isset($this->arrSql['start_day'])) $this->arrSql['start_day'] = "";
  270.         if (!isset($this->arrSql['end_year'])) $this->arrSql['end_year'] = "";
  271.         if (!isset($this->arrSql['end_month'])) $this->arrSql['end_month'] = "";
  272.         if (!isset($this->arrSql['end_day'])) $this->arrSql['end_day'] = "";
  273.         if ( (strlen($this->arrSql['start_year']) > 0 && strlen($this->arrSql['start_month']) > 0 && strlen($this->arrSql['start_day']) > 0 ) ||
  274.                 (strlen($this->arrSql['end_year']) > 0 && strlen($this->arrSql['end_month']) >0 && strlen($this->arrSql['end_day']) > 0) ) {
  275.  
  276.             $arrRegistTime = $this->selectTermRange($this->arrSql['start_year'], $this->arrSql['start_month'], $this->arrSql['start_day']
  277.                             , $this->arrSql['end_year'], $this->arrSql['end_month'], $this->arrSql['end_day'], $regdate_col);
  278.             if (is_array($arrRegistTime)) {
  279.                 foreach ($arrRegistTime as $data4) {
  280.                     $this->arrVal[] = $data4;
  281.                 }
  282.             }
  283.         }
  284.  
  285.         // 最終購入日指定
  286.         if (!isset($this->arrSql['buy_start_year'])) $this->arrSql['buy_start_year'] = "";
  287.         if (!isset($this->arrSql['buy_start_month'])) $this->arrSql['buy_start_month'] = "";
  288.         if (!isset($this->arrSql['buy_start_day'])) $this->arrSql['buy_start_day'] = "";
  289.         if (!isset($this->arrSql['buy_end_year'])) $this->arrSql['buy_end_year'] = "";
  290.         if (!isset($this->arrSql['buy_end_month'])) $this->arrSql['buy_end_month'] = "";
  291.         if (!isset($this->arrSql['buy_end_day'])) $this->arrSql['buy_end_day'] = "";
  292.  
  293.         if ( (strlen($this->arrSql['buy_start_year']) > 0 && strlen($this->arrSql['buy_start_month']) > 0 && strlen($this->arrSql['buy_start_day']) > 0 ) ||
  294.                 (strlen($this->arrSql['buy_end_year']) > 0 && strlen($this->arrSql['buy_end_month']) >0 && strlen($this->arrSql['buy_end_day']) > 0) ) {
  295.             $arrRegistTime = $this->selectTermRange($this->arrSql['buy_start_year'], $this->arrSql['buy_start_month'], $this->arrSql['buy_start_day']
  296.                             , $this->arrSql['buy_end_year'], $this->arrSql['buy_end_month'], $this->arrSql['buy_end_day'], "last_buy_date");
  297.             if (is_array($arrRegistTime)) {
  298.                 foreach ($arrRegistTime as $data4) {
  299.                     $this->arrVal[] = $data4;
  300.                 }
  301.             }
  302.         }
  303.  
  304.         //購入商品コード
  305.         if (!isset($this->arrSql['buy_product_code'])) $this->arrSql['buy_product_code'] = "";
  306.         if ( strlen($this->arrSql['buy_product_code']) > 0 ) {
  307.             $this->setWhere( "customer_id IN (SELECT customer_id FROM dtb_order WHERE order_id IN (SELECT order_id FROM dtb_order_detail WHERE product_code LIKE ? ))");
  308.             $search_buyproduct_code = $this->addSearchStr($this->arrSql['buy_product_code']);
  309.             $this->arrVal[] = $search_buyproduct_code;
  310.         }
  311.  
  312.         //購入商品名称
  313.         if (!isset($this->arrSql['buy_product_name'])) $this->arrSql['buy_product_name'] = "";
  314.         if ( strlen($this->arrSql['buy_product_name']) > 0 ) {
  315.             $this->setWhere( "customer_id IN (SELECT customer_id FROM dtb_order WHERE order_id IN (SELECT order_id FROM dtb_order_detail WHERE product_name LIKE ? ))");
  316.             $search_buyproduct_name = $this->addSearchStr($this->arrSql['buy_product_name']);
  317.             $this->arrVal[] = $search_buyproduct_name;
  318.         }
  319.  
  320.         //カテゴリーを選択している場合のみ絞込検索を行う
  321.         if (!isset($this->arrSql['category_id'])) $this->arrSql['category_id'] = "";
  322.         if ( strlen($this->arrSql['category_id']) != ""){
  323.             //カテゴリーで絞込検索を行うSQL文生成
  324.             list($tmp_where, $tmp_arrval) = $objDb->sfGetCatWhere(SC_Utils_Ex::sfManualEscape($this->arrSql['category_id']));
  325.  
  326.             //カテゴリーで絞込みが可能の場合
  327.             if($tmp_where != "") {
  328.                 $this->setWhere( " customer_id IN (SELECT distinct customer_id FROM dtb_order WHERE order_id IN (SELECT distinct order_id FROM dtb_order_detail WHERE product_id IN (SELECT product_id FROM dtb_product_categories WHERE ".$tmp_where." ))) ");
  329.                 $this->arrVal = array_merge((array)$this->arrVal, (array)$tmp_arrval);
  330.             }
  331.         }
  332.         //携帯電話番号
  333.         if (!isset($this->arrSql['cell'])) $this->arrSql['cell'] = "";
  334.         if ( is_numeric( $this->arrSql['cell'] ) ) {
  335.             $this->setWhere( "(cell01 || cell02 || cell03 LIKE ?)" );
  336.             $searchTel = $this->addSearchStr($this->arrSql['cell']);
  337.             $this->arrVal[] = ereg_replace("-", "", $searchTel);
  338.         }
  339.  
  340.         //キャンペーン
  341.         if (!isset($this->arrSql['campaign_id'])) $this->arrSql['campaign_id'] = "";
  342.         if ( is_numeric( $this->arrSql['campaign_id'] ) ) {
  343.             $this->setWhere( " customer_id IN (SELECT distinct customer_id FROM dtb_campaign_order WHERE campaign_id = ?)" );
  344.             $this->arrVal[] = $this->arrSql['campaign_id'];
  345.         }
  346.  
  347.         //会員状態
  348.         if (!isset($this->arrSql['status'])) $this->arrSql['status'] = "";
  349.         if ( is_array( $this->arrSql['status'] ) ){
  350.             $arrStatusVal = $this->setItemTerm( $this->arrSql['status'] ,"status" );
  351.             foreach ($arrStatusVal as $data) {
  352.                 $this->arrVal[] = $data;
  353.             }
  354.         }
  355.  
  356.         $this->setOrder( "customer_id DESC" );
  357.     }
  358.  
  359.     // 検索用SQL
  360.     function getList() {
  361.         $this->select = "SELECT customer_id,name01,name02,kana01,kana02,sex,email,tel01,tel02,tel03,pref,status FROM dtb_customer ";
  362.         return $this->getSql(0);
  363.     }
  364.  
  365.     function getListMailMagazine($is_mobile = false) {
  366.  
  367.         $colomn = $this->getMailMagazineColumn($is_mobile);
  368.         $this->select = "
  369.             SELECT
  370.                 $colomn
  371.             FROM
  372.                 dtb_customer";
  373.         return $this->getSql(0);
  374.     }
  375.  
  376.     function getMailMagazineColumn($is_mobile= false) {
  377.         if($is_mobile == true) {
  378.             $email_column = "dtb_customer.email_mobile as email";
  379.         } else {
  380.             $email_column = "dtb_customer.email";
  381.         }
  382.  
  383.         $column ="dtb_customer.customer_id,
  384.                 dtb_customer.name01,
  385.                 dtb_customer.name02,
  386.                 dtb_customer.kana01,
  387.                 dtb_customer.kana02,
  388.                 dtb_customer.sex,
  389.                 $email_column,
  390.                 dtb_customer.tel01,
  391.                 dtb_customer.tel02,
  392.                 dtb_customer.tel03,
  393.                 dtb_customer.pref,
  394.                 dtb_customer.create_date,
  395.                 dtb_customer.mailmaga_flg";
  396.  
  397.         return $column;
  398.     }
  399.  
  400.     //　検索総数カウント用SQL
  401.     function getListCount() {
  402.         $this->select = "SELECT COUNT(customer_id) FROM dtb_customer ";
  403.         return $this->getSql(1);
  404.     }
  405.  
  406.     //　CSVダウンロード用SQL
  407.     function getListCSV($arrColumnCSV) {
  408.         $this->arrColumnCSV = $arrColumnCSV;
  409.         $i = 0;
  410.         foreach ($this->arrColumnCSV as $val) {
  411.             if ($i != 0) $state .= ", ";
  412.             $state .= $val["sql"];
  413.             $i ++;
  414.         }
  415.  
  416.         $this->select = "SELECT " .$state. " FROM dtb_customer ";
  417.         return $this->getSql(2);
  418.     }
  419.  
  420.     function getWhere() {
  421.         return array($this->where, $this->arrVal);
  422.     }
  423. }
  424. ?>

Documentation generated on Tue, 28 Apr 2009 18:13:13 +0900 by phpDocumentor 1.4.2